package cn.kgc.dao;

import cn.kgc.entity.Student;
import cn.kgc.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Rock
 * @create 2020-08-22 10:35
 */
public class StudentDaoImpl2 implements StudentDao {
    //将重复代码进行向上抽取，形成update方法
    private void update(String sql, Object... arr) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            //获取数据库连接对象
            con = JDBCUtils.getConnection();
            //准备获取SQL语句的发送对象
            ps = con.prepareStatement(sql);
            // 针对可变参数做非空校验
            if (arr != null) {
                for (int i = 0; i < arr.length; i++) {
                    //因为索引是从0开始,?列从1开始
                    ps.setObject(i+1, arr[i]);
                }
            }
            //发送SQL语句
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            //关闭资源
            JDBCUtils.close(ps, con);
        }
    }

    @Override
    public void addStudent(Student s ) {
        String sql = "insert into student values (null,?,?)";
        update(sql,s.getName(),s.getAge());
    }

    @Override
    public void deleteStudent(int sid) {
        String sql = "delete from student where id = ?";
        update(sql,sid);
    }

    @Override
    public void updateStudent(Student s) {
        String sql = "update student set name = ? ,age = ? where id = ?";
        update(sql,s.getName(),s.getAge(),s.getId());
    }

    @Override
    public List<Student> getAllStudent() {

        Connection con = null;
        ArrayList<Student> list = null;
        PreparedStatement ps =null;
        ResultSet rs = null;
        try {
            //获取数据库连接对象
            con = JDBCUtils.getConnection();
            // 准备SQL语句
            String sql = "select * from student";
            // 准备发送SQL语句的对象
            ps = con.prepareStatement(sql);
            // 发送SQL语句
            rs = ps.executeQuery();
            // 创建集合对象
            list = new ArrayList<Student>();
            // 针对结果集解析
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                Student s = new Student(id,name,age);
                list.add(s);
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtils.close(rs, ps, con);
        }
    }

    @Override
    public Student getStudent(int sid) {

        Connection con = null;
        PreparedStatement ps =null;
        ResultSet rs = null;
        Student s =null;
        try {
            //获取数据库连接对象
            con = JDBCUtils.getConnection();
            // 准备SQL语句
            String sql = "select * from student where id = ?";
            // 准备发送SQL语句的对象
            ps = con.prepareStatement(sql);
            //确定?处的数据
            ps.setInt(1, sid);
            // 发送SQL语句
            rs = ps.executeQuery();
            // 针对结果集解析
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                s = new Student(id,name,age);
                return s;
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtils.close(rs, ps, con);
        }
        return s;
    }
}
